﻿using System;
using System.Collections.Generic;
using System.Data;


namespace EIS.DatabaseHelper
{
    internal class SqlCeTableCreator
    {
        #region Instance Variables

        private SqlCeConnection _connection;
        private string _tableName;

        private SqlCeTransaction _transaction;

        public SqlCeConnection Connection
        {
            get { return _connection; }

            set { _connection = value; }
        }

        public SqlCeTransaction Transaction
        {
            get { return _transaction; }

            set { _transaction = value; }
        }

        public string DestinationTableName
        {
            get { return _tableName; }

            set { _tableName = value; }
        }

        #endregion

        #region Constructor

        public SqlCeTableCreator()
        {
        }

        public SqlCeTableCreator(SqlCeConnection connection) : this(connection, null)
        {
        }

        public SqlCeTableCreator(SqlCeConnection connection, SqlCeTransaction transaction)
        {
            _connection = connection;

            _transaction = transaction;
        }

        #endregion

        #region Instance Methods

        public object Create(DataTable schema)
        {
            return Create(schema, null);
        }

        public object Create(DataTable schema, int numKeys)
        {
            var primaryKeys = new int[numKeys];
            return Create(schema, primaryKeys);
        }

        public object Create(DataTable schema, int[] primaryKeys)
        {
            string sql = GetCreateSQL(_tableName, schema, primaryKeys);


            SqlCeCommand cmd;

            if (_transaction != null && _transaction.Connection != null)

                cmd = new SqlCeCommand(sql, _connection, _transaction);

            else

                cmd = new SqlCeCommand(sql, _connection);


            return cmd.ExecuteNonQuery();
        }

        public object CreateFromDataTable(DataTable table)
        {
            string sql = GetCreateFromDataTableSQL(_tableName, table);


            SqlCeCommand cmd;

            if (_transaction != null && _transaction.Connection != null)

                cmd = new SqlCeCommand(sql, _connection, _transaction);

            else

                cmd = new SqlCeCommand(sql, _connection);


            return cmd.ExecuteNonQuery();
        }

        #endregion

        #region Static Methods

        public static string GetCreateSQL(string tableName, DataTable schema, int[] primaryKeys)
        {
            string sql = "CREATE TABLE " + tableName + " (\n";


            // columns

            foreach (DataRow column in schema.Rows)
            {
                if (!(schema.Columns.Contains("IsHidden") && (bool) column["IsHidden"]))

                    sql += column["ColumnName"] + " " + SQLGetType(column) + ",\n";
            }

            sql = sql.TrimEnd(new[] {',', '\n'}) + "\n";


            // primary keys

            string pk = "CONSTRAINT PK_" + tableName + " PRIMARY KEY CLUSTERED (";

            bool hasKeys = (primaryKeys != null && primaryKeys.Length > 0);

            if (hasKeys)
            {
                // user defined keys

                foreach (int key in primaryKeys)
                {
                    pk += schema.Rows[key]["ColumnName"] + ", ";
                }
            }

            else
            {
                // check schema for keys

                string keys = string.Join(", ", GetPrimaryKeys(schema));

                pk += keys;

                hasKeys = keys.Length > 0;
            }

            pk = pk.TrimEnd(new[] {',', ' ', '\n'}) + ")\n";

            if (hasKeys) sql += pk;

            sql += ")";


            return sql;
        }

        public static string GetCreateFromDataTableSQL(string tableName, DataTable table)
        {
            string sql = "CREATE TABLE [" + tableName + "] (\n";

            // columns

            foreach (DataColumn column in table.Columns)
            {
                sql += "[" + column.ColumnName + "] " + SQLGetType(column) + ",\n";
            }

            sql = sql.TrimEnd(new[] {',', '\n'}) + "\n";

            // primary keys

            if (table.PrimaryKey.Length > 0)
            {
                sql += "CONSTRAINT [PK_" + tableName + "] PRIMARY KEY CLUSTERED (";

                foreach (DataColumn column in table.PrimaryKey)
                {
                    sql += "[" + column.ColumnName + "],";
                }

                sql = sql.TrimEnd(new[] {','}) + "))\n";
            }


            return sql;
        }

        public static string[] GetPrimaryKeys(DataTable schema)
        {
            var keys = new List<string>();


            foreach (DataRow column in schema.Rows)
            {
                if (schema.Columns.Contains("IsKey") && (bool) column["IsKey"])

                    keys.Add(column["ColumnName"].ToString());
            }


            return keys.ToArray();
        }

        // Return T-SQL data type definition, based on schema definition for a column

        public static string SQLGetType(object type, int columnSize, int numericPrecision, int numericScale)
        {
            switch (type.ToString())
            {
                case "System.String":

                    return "VARCHAR(" + ((columnSize == -1) ? 255 : columnSize) + ")";


                case "System.Decimal":

                    if (numericScale > 0)

                        return "REAL";

                    else if (numericPrecision > 10)

                        return "BIGINT";

                    else

                        return "INT";


                case "System.Double":

                case "System.Single":

                    return "REAL";


                case "System.Int64":

                    return "BIGINT";


                case "System.Int16":

                case "System.Int32":

                    return "INT";


                case "System.DateTime":

                    return "DATETIME";


                default:

                    throw new Exception(type + " not implemented.");
            }
        }

        // Overload based on row from schema table

        public static string SQLGetType(DataRow schemaRow)
        {
            return SQLGetType(schemaRow["DataType"],
                              int.Parse(schemaRow["ColumnSize"].ToString()),
                              int.Parse(schemaRow["NumericPrecision"].ToString()),
                              int.Parse(schemaRow["NumericScale"].ToString()));
        }

        // Overload based on DataColumn from DataTable type

        public static string SQLGetType(DataColumn column)
        {
            return SQLGetType(column.DataType, column.MaxLength, 10, 2);
        }

        #endregion
    }
}